import { RowDataPacket } from 'mysql2';
import pool from '../config/database';

export interface IWord extends RowDataPacket {
  id: number;
  word: string;
  pronunciation: string;
  meaning: string;
  example: string;
  category_id: number;
  difficulty: 'easy' | 'medium' | 'hard';
  created_at: Date;
  updated_at: Date;
}

// 创建单词表
export const createWordTable = async (): Promise<void> => {
  const createTableSQL = `
    CREATE TABLE IF NOT EXISTS words (
      id INT PRIMARY KEY AUTO_INCREMENT,
      word VARCHAR(100) UNIQUE NOT NULL,
      pronunciation VARCHAR(100) NOT NULL,
      meaning TEXT NOT NULL,
      example TEXT NOT NULL,
      category_id INT NOT NULL,
      difficulty ENUM('easy', 'medium', 'hard') DEFAULT 'medium',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (category_id) REFERENCES categories(id)
    )
  `;

  try {
    await pool.query(createTableSQL);
    console.log('Words table created or already exists');
  } catch (error) {
    console.error('Error creating words table:', error);
    throw error;
  }
};

// 单词相关操作
export const Word = {
  // 创建单词
  async create(wordData: Omit<IWord, 'id' | 'created_at' | 'updated_at'>): Promise<IWord> {
    const [result] = await pool.query<IWord[]>(
      'INSERT INTO words (word, pronunciation, meaning, example, category_id, difficulty) VALUES (?, ?, ?, ?, ?, ?)',
      [wordData.word, wordData.pronunciation, wordData.meaning, wordData.example, wordData.category_id, wordData.difficulty]
    );
    return result[0];
  },

  // 获取所有单词
  async findAll(): Promise<IWord[]> {
    const [rows] = await pool.query<IWord[]>(
      'SELECT * FROM words'
    );
    return rows;
  },

  // 根据ID获取单词
  async findById(id: number): Promise<IWord | null> {
    const [rows] = await pool.query<IWord[]>(
      'SELECT * FROM words WHERE id = ?',
      [id]
    );
    return rows[0] || null;
  },

  // 根据分类获取单词
  async findByCategory(categoryId: number): Promise<IWord[]> {
    const [rows] = await pool.query<IWord[]>(
      'SELECT * FROM words WHERE category_id = ?',
      [categoryId]
    );
    return rows;
  },

  // 更新单词
  async update(id: number, data: Partial<IWord>): Promise<boolean> {
    const [result] = await pool.query(
      'UPDATE words SET ? WHERE id = ?',
      [data, id]
    );
    return (result as any).affectedRows > 0;
  },

  // 删除单词
  async delete(id: number): Promise<boolean> {
    const [result] = await pool.query(
      'DELETE FROM words WHERE id = ?',
      [id]
    );
    return (result as any).affectedRows > 0;
  }
}; 